package sql;

import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;


class InerstClass{
    //向表中添加数据
    public PreparedStatement insert(String name,int id,Connection connection) throws SQLException {
        String sql = "insert into student values(?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.setString(2,name);
        return statement;
    }
}
class DeleteClass{
    //删除表中数据
    public PreparedStatement delete(int id,Connection connection)throws SQLException{
        String sql = "delete from student WHERE id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);
        return statement;
    }
}

class  UpdateClass{
    //修改表中数据
    public PreparedStatement updete(int id,String name,Connection connection)throws SQLException{
        String sql = "update student set name = ? where id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,name);
        statement.setInt(2,id);
        return statement;
    }
}

class SelectClass{

    //查询表中数据
    public PreparedStatement select(Connection connection)throws SQLException{
        String sql = "select id,name from student";
        PreparedStatement statement = connection.prepareStatement(sql);

        ResultSet resultSet = statement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            System.out.println("id: " + id + ", name: " + name);
        }
        resultSet.close();
        return statement;
    }
}

public class Student {
    public static void menu(){
        System.out.println("---------------------------");
        System.out.println("        1、添加");
        System.out.println("        2、删除");
        System.out.println("        3、修改");
        System.out.println("        4、查询");
        System.out.println("        5、退出");
        System.out.println("---------------------------");
    }
    public static void main(String[] args) throws SQLException {
        Scanner scanner = new Scanner(System.in);
        String name;
        int id;
        while (true){
            DataSource dataSource = new MysqlDataSource();
            ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/my_test1?characterEncoding=utf8&serverTimezone=GMT&useSSL=false");
            ((MysqlDataSource)dataSource).setUser("root");
            ((MysqlDataSource)dataSource).setPassword("123456");
            Connection connection = dataSource.getConnection();
            PreparedStatement statement = null;
            int n = 0;
            menu();
            switch (scanner.nextInt()){
                case 1:
                    System.out.println("请输入要添加的姓名");
                    scanner.nextLine();
                    name = scanner.nextLine();
                    System.out.println("请输入id");
                    id = scanner.nextInt();
                    statement = new InerstClass().insert(name,id,connection);
                    n = statement.executeUpdate();
                    System.out.println("n=" + n);
                    break;
                case 2:
                    System.out.println("请输入id");
                    id = scanner.nextInt();
                    statement = new DeleteClass().delete(id,connection);
                    n = statement.executeUpdate();
                    System.out.println("n=" + n);
                    break;
                case 3:
                    System.out.println("请输入要修改的人物id");
                    id = scanner.nextInt();
                    System.out.println("请输入修改后的人物姓名");
                    scanner.nextLine();
                    name = scanner.nextLine();
                    statement = new UpdateClass().updete(id,name,connection);
                    n = statement.executeUpdate();
                    System.out.println("n=" + n);
                    break;
                case 4:
                    statement = new SelectClass().select(connection);
                    break;
                case 5:
                    connection.close();
                    System.exit(0);
                    break;
                default:
                    break;
            }
            statement.close();
            connection.close();
        }
    }
}
